﻿Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
Public Class oReportParameter
    Public Function GetReportParameterByReportID(ByVal dataBaseName As String, ByVal reportID As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oReportParameter")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT T0.*,T3.ReferenceName")
                query.Append(" FROM oReportParameter T0 JOIN oReport T1 ON T0.ReportID = T1.ReportID")
                query.Append(" LEFT JOIN oReferenceValue T3 ON (T3.ReferenceValue = T0.ParaType AND T3.ReferenceCode ='ParaType')")
                query.Append("  WHERE T0.ReportID = @Param1")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {reportID})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetReportParameterByReportDetailID(ByVal dataBaseName As String, ByVal reportDetailID As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oReportParameter")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT T0.*,T3.ReferenceName")
                query.Append(" FROM oReportParameter T0 JOIN oReport T1 ON T0.ReportID = T1.ReportID")
                query.Append(" LEFT JOIN oReferenceValue T3 ON (T3.ReferenceValue = T0.ParaType AND T3.ReferenceCode ='ParaType')")
                query.Append("  WHERE T0.ReportDetailID = @Param1")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {reportDetailID})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function CreateReportParameter(ByVal dataBaseName As String, ByVal reportID As String, ByVal isUpdate As Boolean, ByVal lineJson As String) As String
        Dim connect As New Connection()
        Try
            Dim jss As New JavaScriptSerializer()
            Dim lstLine As List(Of oLineReportParameter) = jss.Deserialize(Of List(Of oLineReportParameter))(lineJson)
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            Dim query As New StringBuilder
            If errMsg.Length = 0 AndAlso lstLine IsNot Nothing Then
                If isUpdate = False AndAlso reportID = "0" Then
                    query.Append("INSERT INTO [oReportParameter] ")
                    query.Append(" ([ReportID]")
                    query.Append(" ,[ParaName]")
                    query.Append(" ,[ParaCaption]")
                    query.Append(" ,[ParaType]")
                    query.Append(" ,[ParaFromQuery])")
                    query.Append("VALUES (@ReportID{0},@ParaName{0},@ParaCaption{0},@ParaType{0},@ParaFromQuery{0})")
                    Dim count As Integer = connect.Object_Execute_For_Report(query.ToString(), reportID, lstLine)
                Else
                    Dim params = Nothing
                    For Each line In lstLine
                        Dim item As New oItem
                        query = New StringBuilder
                        If line.LineStatus = "1" Then
                            query.Append("INSERT INTO [oReportParameter] ")
                            query.Append(" ([ReportID]")
                            query.Append(" ,[ParaName]")
                            query.Append(" ,[ParaCaption]")
                            query.Append(" ,[ParaType]")
                            query.Append(" ,[ParaFromQuery])")
                            query.Append(" VALUES (@Param1,@Param2,@Param3,@Param4,@Param5)")

                            Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {reportID, line.ParaName, line.ParaCaption, line.ParaType, _
                                                                                                  line.ParaFromQuery})
                        ElseIf line.LineStatus = "2" Then
                            query.Append("IF EXISTS (SELECT 1 FROM oReportParameter WHERE [ReportID] = @Param5  AND [ReportDetailID] = @Param6  )")
                            query.Append("  UPDATE [oReportParameter] SET ")
                            query.Append("  [ParaName] = @Param1")
                            query.Append(" ,[ParaCaption] = @Param2")
                            query.Append(" ,[ParaType] = @Param3")
                            query.Append(" ,[ParaFromQuery] = @Param4")
                            query.Append(" WHERE [ReportID] = @Param5 AND [ReportDetailID] = @Param6")

                            Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {line.ParaName, line.ParaCaption, line.ParaType, line.ParaFromQuery, _
                                                                                                 line.ReportID, line.ReportDetailID})
                        ElseIf line.LineStatus = "3" Then
                            query.Append("IF EXISTS (SELECT 1 FROM oReportParameter WHERE [ReportID] = @Param1  AND [ReportDetailID] = @Param2  )")
                            query.Append("  DELETE FROM [oReportParameter]")
                            query.Append("  WHERE [ReportID] = @Param1 AND ReportDetailID = @Param2")
                            Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {reportID, line.ReportDetailID})
                        End If
                    Next
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Class oLineReportParameter
        Property ReportDetailID As Integer
        Property ReportID As Integer
        Property ParaName As String
        Property ParaCaption As String
        Property ParaType As String
        Property ParaFromQuery As String
        Property LineStatus As String
        Public Sub New()
        End Sub
    End Class
End Class
